﻿
CREATE procedure [dbo].[FC_SegmentPL_Search] 
@IdCliente int,
@Year int = NULL
as
begin
SELECT 0 AS IdSegmentPL, '' AS DesSegmentPL
UNION ALL

SELECT SPL.IdSegmentPL, SEG.DesSegment + ' \ '+ PL.DesProductLine as DesSegmentPL
FROM FC_Customer CU 
LEFT JOIN FC_SalePrice SP ON CU.IdCustomer = SP.IdCustomer
LEFT JOIN FC_Product PR ON SP.IdProduct = PR.IdProduct
LEFT JOIN FC_SegmentPL SPL ON PR.IdSegmentPL = SPL.IdSegmentPL
inner join FC_Segment SEG on SPL.IdSegment = SEG.IdSegment 
inner join FC_ProductLine PL ON SPL.IdProductLine = PL.IdProductLine
WHERE CU.IdCustomer = @IdCliente
GROUP BY SPL.IdSegmentPL, SEG.DesSegment, PL.DesProductLine 


end